from __future__ import print_function
# from clickhouse_driver import Client
from clickhouse_driver import connect
import json
import os
import six
host='127.0.0.1' #服务器地址
port=9000 #端口
user='default' #用户名
password='' #密码
send_receive_timeout = 300  #超时时间
# client = Client(host=host, port=port, user=user, password=password, send_receive_timeout=send_receive_timeout)
conn = connect(host=host, port=port, user=user, password=password, database='github_log')
cursor = conn.cursor()
# cursor.execute('SHOW TABLES')

if __name__ == "__main__":
    # 10000
    sql = "select id, created_at, type, body, repo_name from github_log.events where type in ('IssueCommentEvent', 'PullRequestReviewCommentEvent') order by created_at limit 250001, 50000"
    # sql = "select id, body from github_log.events where type in ('IssueCommentEvent', 'PullRequestReviewCommentEvent') order by id limit 1"
    cursor.execute(sql)
    ans = cursor.fetchall()
    count = 0
    for obj in ans:
        print(obj)
        # 把要测试的短文本以str格式放到这个列表里
        test_text = [
            obj[3][:256]
        ]

        # 遍历分析每个短文本
        for index, text in enumerate(test_text):
            results[index]["text"] = text
        for index, result in enumerate(results):
            if six.PY2:
                print('----------***--------***')
                print(obj[0])
                print(
                    json.dumps(results[index], encoding="utf8", ensure_ascii=False))
            else:
                # print('text: {},\t  predict: {}'.format(results[index]['text'], results[index]['sentiment_key']))
                sent = 0
                if results[index]['sentiment_key'] == 'positive':
                    sent = 1
                if results[index]['sentiment_key'] == 'negative':
                    sent = 2
                # sqlInsert = "insert into github_log.sentiment(id, type,  body, created_at, repo_name, sentiment) values(" + str(obj[0]) + ", '" + str(obj[2]) + "', " + str(obj[3]) + ", '" + obj[1].strftime("%Y-%m-%d %H:%M:%S") + "', '" + obj[4] + "'," + str(sent) +")"
                sqlInsert = "insert into github_log.sentiment(id, type, body, created_at, repo_name, sentiment) values"
                values = [
                    {
                        "id": obj[0],
                        "type": str(obj[2]),
                        "body": str(obj[3]),
                        "created_at": obj[1],
                        "repo_name": obj[4],
                        "sentiment": sent
                    }
                ]
                # sqlUp = "alter table github_log.events update sentiment = " + str(sent) + " where id = " + str(obj[0])
                print(sqlInsert)
                cursor.execute(sqlInsert, values)
                result = cursor.rowcount
                if result != 1:
                    print('----------***--------***')
                    print(obj[0])
        count += 1
        print(count)
    cursor.close()
    conn.close()





